const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 * 商品上架2019-04-13 00:00:01.000
 */
// 上架的开始时间
let startTime = '2019-05-17 00:00:01.000';

/**
 * 
 * 获取pc上架商品的信息
 */
const getUpperShelfShop = async (ctx, next) => {
    const { pageNum, searchVal: { rkrq, gys, ypbm, isUpperShelf, time } } = ctx.request.body;
    let searchActionList = [];
    let publicSql = `FROM (SELECT RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.JXMC,RM.SCCJ,RM.PZWH,RM.SL,RM.SCPH,RM.YXQZ,RM.HWBH,RM.ID,RM.YPTM,
    RZ.DWBM,RZ.RQ FROM RKD_MX RM JOIN RKD_ZB RZ ON RM.ID = RZ.ID UNION ALL SELECT XM.YPBM,XM.YPMC,XM.YPGG,XM.JLDW,XM.JXMC,XM.SCCJ,XM.PZWH,XM.SL,XM.SCPH,XM.YXQZ,
    XM.HWBH,XM.ID,XM.YPTM,XZ.DWBM,XZ.RQ FROM XTD_MX XM JOIN XTD_ZB XZ ON XM.ID = XZ.ID) M  LEFT JOIN BM_YP B ON M.YPBM = B.BM LEFT JOIN YPSJ Y ON M.ID = Y.ID AND 
    M.YPBM = Y.YPBM AND M.SCPH = Y.SCPH LEFT JOIN BM_WLDW DW ON M.DWBM = DW.BM WHERE M.RQ>'${startTime}' `;

    let startSql = `SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY M.YPBM) AS px,Y.ID AS UP, M.ID,DW.MC AS DWMC,M.YPBM,M.YPTM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,
    M.SL AS DJSL,(ISNULL(M.SL,0)-ISNULL(Y.SJSL,0)) AS SL,M.SCPH,M.YXQZ,M.HWBH,B.CCTJ,B.YPLX ${publicSql} `;

    let totalSql = `SELECT COUNT(M.YPBM) AS total  ${publicSql} `;
    if (ypbm) {
        searchActionList.push(` AND (M.YPBM LIKE '%${ypbm}%' OR M.YPMC LIKE '%${ypbm}%' OR M.YPTM LIKE '%${ypbm}%' OR B.JP LIKE '%${ypbm}%')`);
    }
    if (gys) {
        searchActionList.push(` AND (DW.BM LIKE '%${gys}%' OR DW.MC LIKE '%${gys}%' OR DW.JP LIKE '%${gys}%' )`);
    }
    if (0 in rkrq) {
        searchActionList.push(` AND M.RQ >= '${rkrq[0]} 00:00:00' AND M.RQ <= '${rkrq[1]} 23:59:59' `);
    }
    if (isUpperShelf == 1) {
        if (0 in time) {
            searchActionList.push(` AND (Y.ID IS NOT NULL OR M.SL <= Y.SJSL) AND Y.SJRQ>= '${time[0]} 00:00:00' AND Y.SJRQ<= '${time[1]} 23:59:59' `);
        }
    } else {
        searchActionList.push(` AND (Y.ID IS NULL OR M.SL > Y.SJSL) `);
    }
    totalSql += ` ${searchActionList.join(' ')} `;
    startSql += ` ${searchActionList.join(' ')}) AS t1 WHERE t1.px BETWEEN ${(pageNum - 1) * 10 + 1} AND ${pageNum * 10}`;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = { result: result.recordset, total: total.recordset[0].total }
}
// 补全时间
const repairTime = (time) => {
    return time.toString().length == 1 ? ('0' + time) : time;
}
// 获取时间拼接的年月日
const getNowTimeId = (time) => {
    let nowTime = time ? new Date(time) : new Date();
    let y = nowTime.getFullYear();
    let M = nowTime.getMonth() + 1;
    let d = nowTime.getDate();
    let h = nowTime.getHours();
    let m = nowTime.getMinutes();
    let s = nowTime.getSeconds();
    return {
        msDate: repairTime(m) + repairTime(s),
        date: y.toString().slice(2) + repairTime(M) + repairTime(d),
        fullDate: `${y.toString()}-${repairTime(M)}-${repairTime(d)} ${repairTime(h)}:${repairTime(m)}:${repairTime(s)}.000`
    }
}
/**
 * 
 * 上架商品
 */
const upperShelfShop = async (ctx, next) => {
    const body = ctx.request.body;
    console.log(body);
    // if (body.shopInfo.hasOwnProperty('XTM')) {
    //     if ((!body.shopInfo.YPTM) && body.shopInfo.XTM) {
    //         let updateSql = `UPDATE BM_YP SET TM ='${body.shopInfo.XTM}' WHERE BM = '${body.shopInfo.YPBM}'`;
    //         sql.query(updateSql);
    //     }
    // }
    // let isExistSql = `SELECT COUNT(ID) AS PX ,SJSL  FROM YPSJ WHERE ID = '${body.shopInfo.ID}' AND YPBM = '${body.shopInfo.YPBM}' AND SCPH = '${body.shopInfo.SCPH}' GROUP BY SJSL`;
    // let isExist = await sql.query(isExistSql);
    // let actionSql = '';
    // if (isExist.recordset[0] && isExist.recordset[0].PX > 0) {
    //     actionSql = `UPDATE YPSJ SET SJSL =${Number(body.shopInfo.SJSL) + Number(isExist.recordset[0].SJSL)} WHERE  ID = '${body.shopInfo.ID}' AND YPBM = '${body.shopInfo.YPBM}' AND SCPH = '${body.shopInfo.SCPH}' `;

    // } else {
    //     actionSql = `INSERT INTO YPSJ VALUES ('${body.shopInfo.ID}','${body.shopInfo.YPBM}','${body.shopInfo.SCPH}','${getNowTimeId().fullDate}','${body.username}',${body.shopInfo.SJSL}) `;

    // }
    // let result = await sql.query(actionSql);
    // if (result.rowsAffected[0] > 0) {
    //     ctx.body = { success: true }
    // } else {
    //     ctx.body = { success: false }
    // }
}

/**
 * @params {day}距离今天是多少天
 * @description 获取距离今天多少天的时间
 * @value 时间格式
 */
// 获得三天前的时间 
const getDayAgo = (day) => {
    let nowStamp = (new Date()).getTime();
    let threeAgoStamp = nowStamp - day * 24 * 60 * 60 * 1000;
    let threeAgoTime = new Date(threeAgoStamp);
    let y = threeAgoTime.getFullYear();
    let m = threeAgoTime.getMonth() + 1;
    let d = threeAgoTime.getDate();
    return y + '-' + (m < 10 ? ('0' + m) : m) + '-' + (d < 10 ? ('0' + d) : d);
}
/**
 * 
 * 获取移动端上架商品的信息
 */
const getPhoneUpperShelfShop = async (ctx, next) => {
    const body = ctx.request.body;
    let searchVal = body.searchVal;
    let startSql = `SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER BY M.YPBM) AS px,M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,
    (ISNULL(M.SL,0)-ISNULL(Y.SJSL,0)) AS SL,M.SCPH,M.YXQZ,M.HWBH,B.CCTJ,B.YPLX,M.ID,M.DWMC,M.YPTM FROM 
    (SELECT RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.JXMC,RM.SCCJ,RM.PZWH,SUM(RM.SL) AS SL,RM.SCPH,RM.YXQZ,RM.HWBH,RM.ID,RM.YPTM,RZ.DWMC,RZ.RQ 
    FROM RKD_MX RM JOIN RKD_ZB RZ ON RM.ID = RZ.ID GROUP BY RM.YPBM,RM.YPMC,RM.YPGG,RM.JLDW,RM.JXMC,RM.SCCJ,RM.PZWH,RM.SCPH,RM.YXQZ,
    RM.HWBH,RM.ID,RM.YPTM,RZ.DWMC,RZ.RQ UNION ALL SELECT XM.YPBM,XM.YPMC,XM.YPGG,XM.JLDW,XM.JXMC,XM.SCCJ,XM.PZWH,SUM(XM.SL),XM.SCPH,
    XM.YXQZ,XM.HWBH,XM.ID,XM.YPTM,XZ.DWMC,XZ.RQ FROM XTD_MX XM JOIN XTD_ZB XZ ON XM.ID = XZ.ID WHERE XZ.SH = 'T' GROUP BY XM.YPBM,XM.YPMC,XM.YPGG,XM.JLDW,
    XM.JXMC,XM.SCCJ,XM.PZWH,XM.SCPH,XM.YXQZ,XM.HWBH,XM.ID,XM.YPTM,XZ.DWMC,XZ.RQ ) M LEFT JOIN BM_YP B ON M.YPBM = B.BM LEFT JOIN YPSJ Y 
    ON M.ID = Y.ID AND M.YPBM = Y.YPBM AND M.SCPH = Y.SCPH WHERE M.RQ>'${startTime}' AND M.RQ>'${getDayAgo(15)} 00:00:00.000'`;
    if (searchVal) {
        startSql += ` AND (M.YPBM LIKE '%${searchVal}%' OR M.YPMC LIKE '%${searchVal}%' OR M.YPTM LIKE '%${searchVal}%' OR B.JP LIKE '%${searchVal}%')`;
    }
    startSql += ` AND (Y.ID IS NULL OR M.SL > Y.SJSL)) AS t1  WHERE t1.px BETWEEN ${(body.page - 1) * 10 + 1} AND ${body.page * 10} `;
    let result = await sql.query(startSql);
    ctx.body = { result: result.recordset }
}







module.exports = {
    'POST /upperShelfShop': upperShelfShop,
    'POST /getUpperShelfShop': getUpperShelfShop,
    'POST /getPhoneUpperShelfShop': getPhoneUpperShelfShop


}